package com.hoas.comparedb.mysql;

import com.hoas.comparedb.core.CompareSource;
import com.hoas.comparedb.core.DBReader;
import com.hoas.comparedb.entity.Table;
import com.hoas.comparedb.entity.TableColumn;
import com.hoas.comparedb.entity.TableIndex;
import com.hoas.comparedb.util.StrTool;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MySQLReader implements DBReader {

    private CompareSource source;

    private DataSource ds;

    private String db;

    public MySQLReader(CompareSource source, String db) {

        this.source = source;
        this.ds = DSFactory.createDataSource(source.getDriver(), source.getUrl(), source.getUser(), source.getPass());;
        this.db = db == null || db.length() == 0 ? source.getDb() : db;
    }

    @Override
    public List<Table> getTables( String table ) throws SQLException {

        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner(ds);
        // 2.执行查询
        String sql = "SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.TABLE_COMMENT, t.TABLE_COLLATION, ts.CHARACTER_SET_NAME" +
                " FROM information_schema.TABLES t,information_schema.COLLATION_CHARACTER_SET_APPLICABILITY ts" +
                " WHERE ts.collation_name = t.table_collation" +
                " and table_schema = ?";

        List<String> params = new ArrayList<>();
        params.add( db );

        if (table != null) {
            sql += " AND table_name = ?";
            params.add( table );

        }

        List<Table> tables = qr.query(sql, new BeanListHandler<>(Table.class,buildProcessor()), params.toArray());
        return tables;
    }


    @Override
    public String getTableStructure(Table table) throws SQLException {

        QueryRunner qr = new QueryRunner(ds);
        String sql = "show create table " + table.getTableName();
        return  qr.query(sql,new ScalarHandler<String>(2));

    }

    @Override
    public List<TableColumn> getColumns(Table table ) throws SQLException {

        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner(ds);
        // 2.执行查询
        String sql = "SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION COLUMN_POSITION, COLUMN_TYPE, COLUMN_DEFAULT, IS_NULLABLE, COLUMN_KEY, EXTRA, COLUMN_COMMENT, CHARACTER_SET_NAME, COLLATION_NAME" +
                " FROM information_schema.COLUMNS" +
                " WHERE table_schema = ? and table_name = ?";
        List<TableColumn> columns = qr.query(sql, new BeanListHandler<>(TableColumn.class,buildProcessor()),db, table.getTableName());

       columns.forEach( r -> {

           String extra = r.getExtra();
           if (StrTool.containsIgnoreCase(extra, "auto_increment")){
               r.setAutoIncr( true );
           }

       });

        return columns;
    }

    @Override
    public List<TableIndex> getIndexes(Table table ) throws SQLException {


        // 1.创建QueryRunner对象
        QueryRunner qr = new QueryRunner(ds);
        // 2.执行查询
        String sql = "select TABLE_SCHEMA, TABLE_NAME, index_name, GROUP_CONCAT(column_name order by SEQ_IN_INDEX) as composit_index, INDEX_TYPE, INDEX_COMMENT, NON_UNIQUE" +
                " from information_schema.statistics" +
                " where table_schema = ? and table_name = ?" +
                " group by index_name, table_name";
        List<TableIndex> indices = qr.query(sql, new BeanListHandler<>(TableIndex.class,buildProcessor()),db, table.getTableName());
        return indices;
    }

    private RowProcessor buildProcessor() {
        //开启下划线->驼峰转换所用
        BeanProcessor bean = new GenerousBeanProcessor();
        RowProcessor processor = new BasicRowProcessor(bean);
        return processor;
    }


    @Override
    public String getDb() {
        return db;
    }

    @Override
    public CompareSource getSource() {
        return source;
    }

}
